﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Reflection;
using System.Globalization;

namespace Librarian.Shared
{
    public static class SearchEngine
    {
        const string main = @"select distinct(books.id), Title, authors.name Author, themes.name Theme, types.name Type, locations.name Location, Comments, Year
from books
join authors on books.author=authors.id
join locations on books.location=locations.id
join themes on books.theme=themes.id
join types on books.type=types.id ";
        const string ezQueryTitle=" titled like @{0} ";
        const string ezQueryComments=" commentsd like @{0} ";
        const string ezQueryLocation = " locations.named like @{0} ";
        const string ezQueryAuthor = " authors.named like @{0} ";
        const string ezQueryTheme = " themes.named like @{0} ";
        const string ezQueryType = " types.named like @{0} ";
        const string ezQueryKeywordsWhere = " keywords.named like @{0} ";
        const string ezQueryKeywordsJoin=@"
left outer join keywordsofbook on books.id=bookid
left outer join keywords on keywords.id=keywordid";
        const string ezYearFilter = " Year {0} @YEAR1 ";
        const string ezYearRangeFilter = " ( Year >= @YEAR1 AND Year <= @YEAR2)";

        public static DataTable FindText(string texta)
        {
            return FindText(texta, EzSearchOptions.All,null);
        }

        public static DataTable FindText(string texta, EzSearchOptions options)
        {
            return FindText(texta, options, null);
        }

        public static DataTable FindText(string texta, EzSearchOptions options, YearFilter yearFilter)
        {
            StringBuilder ezSelectBuilder = new StringBuilder();
            List<string> ezWhereBuilder = new List<string>();

            ezSelectBuilder.Append(main);

            string[] strings = texta.Split(' ');

            //append join if needed for keywords
            if (options.HasValue(EzSearchOptions.Keyword))
            {
                ezSelectBuilder.Append(ezQueryKeywordsJoin);
            }

            for (int i = 0; i < strings.Length; i++)
            {

                if (options.HasValue(EzSearchOptions.Author))
                {
                    ezWhereBuilder.Add(string.Format(ezQueryAuthor,i));
                }
                if (options.HasValue(EzSearchOptions.Comments))
                {
                    ezWhereBuilder.Add(string.Format(ezQueryComments,i));
                }
                if (options.HasValue(EzSearchOptions.Keyword))
                {
                    ezWhereBuilder.Add(string.Format(ezQueryKeywordsWhere,i));
                }
                if (options.HasValue(EzSearchOptions.Location))
                {
                    ezWhereBuilder.Add(string.Format(ezQueryLocation,i));
                }
                if (options.HasValue(EzSearchOptions.Theme))
                {
                    ezWhereBuilder.Add(string.Format(ezQueryTheme,i));
                }
                if (options.HasValue(EzSearchOptions.Title))
                {
                    ezWhereBuilder.Add(string.Format(ezQueryTitle,i));
                }
                if (options.HasValue(EzSearchOptions.Type))
                {
                    ezWhereBuilder.Add(string.Format(ezQueryType,i));
                }
            }

            ezSelectBuilder.Append(" where ");
            if (ezWhereBuilder.Count > 0)
                ezSelectBuilder.Append(
                    string.Format(" ({0}) ",
                    string.Join(" or ", ezWhereBuilder.ToArray())));
            else
                ezSelectBuilder.Append(" 0=1 ");

            if (yearFilter != null)
            {
                string comparer=null;
                switch (yearFilter.DateRange)
                {
                    case Comparison.Equal:
                        comparer = "=";
                        break;
                    case Comparison.GreaterThan:
                        comparer = ">=";
                        break;
                    case Comparison.SmallerThan:
                        comparer = "<=";
                        break;
                }

                ezSelectBuilder.Append(" AND ");

                if (comparer != null)
                {
                    ezSelectBuilder.Append(
                        string.Format(CultureInfo.InvariantCulture,
                        ezYearFilter, comparer));
                }
                else if (yearFilter.DateRange == Comparison.Between)
                {
                    ezSelectBuilder.Append(ezYearRangeFilter);
                }
            }

            using (System.Data.SQLite.SQLiteConnection conn =
                new System.Data.SQLite.SQLiteConnection(BookRules.GetConnectionString()))
            {
                conn.Open();

                using (SQLiteCommand command = new SQLiteCommand(ezSelectBuilder.ToString(), conn))
                {
                    for (int i = 0; i < strings.Length; i++)
                    {
                        command.Parameters.Add(string.Format("@{0}", i), DbType.String).Value =
                            string.Format("%{0}%", strings[i].RemoveAccents());
                    }

                    if (yearFilter != null)
                    {
                        command.Parameters.Add("@YEAR1",DbType.Int32).Value= yearFilter.MinYear;
                        if (yearFilter.DateRange == Comparison.Between)
                        {
                            command.Parameters.Add("@YEAR2", DbType.Int32).Value = yearFilter.MaxYear;
                        }
                    }

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        DataTable t = createTable(reader);
                        return t;
                    }
                }                
            }
        }

        private static DataTable createTable(SQLiteDataReader reader)
        {
            DataTable t = new DataTable();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                t.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
            }
            while (reader.Read())
            {
                object[] values = new object[reader.FieldCount];
                reader.GetValues(values);
                t.Rows.Add(values);
            }
            return t;
        }

        public static DataTable Empty()
        {
            using (System.Data.SQLite.SQLiteConnection conn =
                 new System.Data.SQLite.SQLiteConnection(BookRules.GetConnectionString()))
            {
                conn.Open();

                using (SQLiteCommand command = new SQLiteCommand(main+" where 0=1", conn))
                {
                   using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        DataTable t = createTable(reader);
                        return t;
                    }
                }
            }
        }
    }

    [Flags]
    public enum EzSearchOptions
    {
        None=0x0,
        Title=0x1,
        Comments=0x2,
        Author=0x4,
        Location=0x8,
        Theme=0x10,
        Type=0x20,
        Keyword=0x40,
        All=0x7f
    }

    public enum Comparison
    {
        None=0,
        Equal,
        Between,
        SmallerThan,
        GreaterThan,
        Like
    }

    public class YearFilter
    {
        public Comparison DateRange { get; set; }
        public int MaxYear { get; set; }
        public int MinYear { get; set; }
    }

    public static class EnumHelper
    {
        public static bool HasValue<T>(this T test, T flags) where T: struct
        {
            long checkAgainst = Convert.ToInt64(flags);
            long toCheck = Convert.ToInt64(test);
            return (checkAgainst & toCheck) == checkAgainst;
        }
    }
}
